{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT basics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "eng = hive.connect(host='192.168.31.31', port=10000, auth='NOSASL', \n",
    "                   database='sqlzoo', username='jupyter')\n",
    "\n",
    "%load_ext sql\n",
    "%sql eng\n",
    "%config SqlMagic.displaylimit = 50"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introducing the `world` table of countries\n",
    "\n",
    "The example uses a WHERE clause to show the population of 'France'. Note that strings (pieces of text that are data) should be in 'single quotes';\n",
    "\n",
    "**Modify it to show the population of Germany**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  '<pyhive.hive.Connection object at 0x7f406dfd8910>'\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>population</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>83149300.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(83149300.0,)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT population from world WHERE name='Germany'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Scandinavia\n",
    "\n",
    "Checking a list The word **IN** allows us to check if an item is in a list. The example shows the name and population for the countries 'Brazil', 'Russia', 'India' and 'China'.\n",
    "\n",
    "**Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  '<pyhive.hive.Connection object at 0x7f406dfd8910>'\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name</th>\n",
       "            <th>population</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Denmark</td>\n",
       "            <td>5822763.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Norway</td>\n",
       "            <td>5367580.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sweden</td>\n",
       "            <td>10338368.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Denmark', 5822763.0), ('Norway', 5367580.0), ('Sweden', 10338368.0)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population FROM world WHERE name IN ('Sweden', 'Norway', 'Denmark')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Just the right size\n",
    "\n",
    "Which countries are not too small and not too big? `BETWEEN` allows range checking (range specified is inclusive of boundary values). The example below shows countries with an area of 250,000-300,000 sq. km. Modify it to show the country and the area for countries with an area between 200,000 and 250,000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  '<pyhive.hive.Connection object at 0x7f406dfd8910>'\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name</th>\n",
       "            <th>area</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Belarus</td>\n",
       "            <td>207600.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ghana</td>\n",
       "            <td>238533.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Guinea</td>\n",
       "            <td>245857.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Guyana</td>\n",
       "            <td>214969.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Laos</td>\n",
       "            <td>236800.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Romania</td>\n",
       "            <td>238391.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Uganda</td>\n",
       "            <td>241550.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>United Kingdom</td>\n",
       "            <td>242900.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Belarus', 207600.0),\n",
       " ('Ghana', 238533.0),\n",
       " ('Guinea', 245857.0),\n",
       " ('Guyana', 214969.0),\n",
       " ('Laos', 236800.0),\n",
       " ('Romania', 238391.0),\n",
       " ('Uganda', 241550.0),\n",
       " ('United Kingdom', 242900.0)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
